const SqliteUtil = appRequire('modules/SqliteUtil');
const PageResultVo = appRequire('vo/PageResultVo.js');
class PhoneDatasSqliteDao {
  constructor() {

  }
  static async create() {
    let db = await SqliteUtil.open(appConfig.dbPath2);
    await db.run(`CREATE TABLE "phone_datas" (
      "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
      "first_lv_dept" VARCHAR(255) DEFAULT '',
      "second_lv_dept" VARCHAR(255) DEFAULT '',
      "username" VARCHAR(255),
      "job" VARCHAR(255),
      "phone_num" VARCHAR(255),
      "short_phone_num" VARCHAR(255),
      "f7" VARCHAR(255),
      "is_hide" VARCHAR(10) DEFAULT '',
      "user_sort" integer,
      "first_lv_dept_sort" integer,
      "second_lv_dept_sort" integer DEFAULT 1
    );`);
    await db.close();
  }
  static async saveOne(phoneDatas) {
    let db = await SqliteUtil.open(appConfig.dbPath2);
    try {
      await db.run(`insert into phone_datas
          (first_lv_dept,second_lv_dept,username,job,phone_num,short_phone_num,f7,is_hide,user_sort,first_lv_dept_sort,second_lv_dept_sort) 
          values(?,?,?,?,?,?,?,?,?,?,?)`,
        [phoneDatas.first_lv_dept,phoneDatas.second_lv_dept,phoneDatas.username,phoneDatas.job,phoneDatas.phone_num,phoneDatas.short_phone_num,phoneDatas.f7,phoneDatas.is_hide,phoneDatas.user_sort,phoneDatas.first_lv_dept_sort,phoneDatas.second_lv_dept_sort]);
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async updateOne(phoneDatas) {
    let db = await SqliteUtil.open(appConfig.dbPath2);
    try {
      await db.run(`update phone_datas 
        set first_lv_dept=?,second_lv_dept=?,username=?,job=?,phone_num=?,short_phone_num=?,f7=?,is_hide=?,user_sort=?,first_lv_dept_sort=?,second_lv_dept_sort=? where id = ?`,
        [phoneDatas.first_lv_dept,phoneDatas.second_lv_dept,phoneDatas.username,phoneDatas.job,phoneDatas.phone_num,phoneDatas.short_phone_num,phoneDatas.f7,phoneDatas.is_hide,phoneDatas.user_sort,phoneDatas.first_lv_dept_sort,phoneDatas.second_lv_dept_sort,phoneDatas.id]);
      } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async deleteById(id) {
    let db = await SqliteUtil.open(appConfig.dbPath2);
    try {
      await db.run(`delete from phone_datas where id = ?`,
      [id]);
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async deleteMany(ids) {
    let db = await SqliteUtil.open(appConfig.dbPath2);
    try {
      console.log(ids);
      let sqlWhere = 'where id in (';
      for(let i = 0;i<ids.length;i++){
        if(i!=ids.length-1){
          sqlWhere+=`'${ids[i]}',`
        }else{
          sqlWhere+=`'${ids[i]}')`
        }
      }
      let sql = `delete from phone_datas ${sqlWhere}`;
      console.log(sql);
      await db.run(sql);
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async findOne(id) {
    let db = await SqliteUtil.open(appConfig.dbPath2);
    try {
      let data = await db.get(`select * from phone_datas where id=?`,[id]);
      return data;
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async findById(id) {
    let db = await SqliteUtil.open(appConfig.dbPath2);
    try {
      let data = await db.get(`select * from phone_datas where id=?`,[id]);
      return data;
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async findAll() {
    let db = await SqliteUtil.open(appConfig.dbPath2);
    try {
      let data = await SqliteUtil.each(db,`select * from phone_datas`);
      return data;
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async findByPage(page) {
    let db = await SqliteUtil.open(appConfig.dbPath2);
    try {
      let sqlWhere = '';
      let sqlParams = [(page.pageNum-1)*page.pageSize,page.pageSize];
      if(page.searchParams&&page.searchParams.username){
        sqlWhere +='where username=?'
        sqlParams.unshift(page.searchParams.username);
      }
      let result = await db.get(`select count(*) as totalNum from phone_datas ${sqlWhere}`);
      let count = result.totalNum;
      let data = await SqliteUtil.each(db,`select * from phone_datas ${sqlWhere} limit ?,?`,sqlParams);
      //加了await 会等待data拼装完成
      return PageResultVo.success('success',count,data);
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }   
  }
}
module.exports = PhoneDatasSqliteDao;